package com.zdkj.subway.stm.reportquery.util;


import com.zdkj.subway.stm.entity.SwStReportqueryStationincome;
import com.zdkj.subway.stm.util.DateUtilStm;
import com.zdkj.subway.stm.util.JdbcUtil;
import com.zdkj.subway.stm.util.ObjectValueUtils;
import lombok.extern.slf4j.Slf4j;

import java.math.BigDecimal;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.time.LocalDateTime;
import java.util.*;

/**
 *  车站营收日报表查询接口
 */
@Slf4j
public class  StationSettleUtil {
	/*
	 * 营收日报表查询
	 */
	private static SwStReportqueryStationincome  getStationIncomeReport(Map<String,Object> param) {
		
		String lineCode=String.valueOf(param.get("lineCode"));
	    String stationCode=String.valueOf(param.get("stationCode"));  
	    Date initDate = (Date)param.get("statisticsDate");
	    /*
	    String beginTime = (String)param.get("beginTime");
	    String endTime = (String)param.get("endTime");
	    String submitTime = (String)param.get("submitTime");
	    int settleTerm = (Integer)param.get("settlementTerm");
	    */
	    Calendar calendar = Calendar.getInstance();
        calendar.setTime(initDate);
        //calendar.add(Calendar.DAY_OF_MONTH,-1);
        Date time = calendar.getTime();
        SimpleDateFormat dff = new SimpleDateFormat("yyyy-MM-dd");
        String statisticsDate = dff.format(time);
        
        
        String beginDate = statisticsDate+" 02:00:00";
        String endDate = DateUtilStm.getNextDay(statisticsDate, 1, "yyyy-MM-dd")+" 01:59:59";
        
       //长周期结算的车站，暂时不考虑长周期补币问题，直接查统计日期的相关数据
       /*
		if(settleTerm!=1){
        	if(!endTime.equals(endDate)){//没到结算周期，取endTime作为开始日期范围查询
        		beginDate = endTime.substring(0,10)+" 02:00:00";
        	}else{
        		beginDate = beginTime;	
        		
        	}        	
        }*/
        //钱箱
        
        BigDecimal bigCashBox = new BigDecimal("0.00");
		BigDecimal bigSupplment = new BigDecimal("0.00");
		BigDecimal bigHand = new BigDecimal("0.00");
		BigDecimal bigYZDCP = new BigDecimal("0.00");
		BigDecimal bigYJZP = new BigDecimal("0.00");
		BigDecimal bigOtherYZDCP = new BigDecimal("0.00");
		BigDecimal bigOtherYJZP = new BigDecimal("0.00");
	    BigDecimal bigConductorRealAmount = new BigDecimal("0.00");
		BigDecimal bigMarketGroup = new BigDecimal("0.00");
		BigDecimal bigMarketCondutctorSell = new BigDecimal("0.00");
		BigDecimal bigShort = new BigDecimal("0.00");
		BigDecimal bigAdd = new BigDecimal("0.00");
		BigDecimal bigLong = new BigDecimal("0.00");
        BigDecimal bigOther = new BigDecimal("0.00");
		BigDecimal bigCashBoxUnload = new BigDecimal("0.00");
		String name1="";
        String no1="";
        String name2="";
        String no2="";
		String otherRemark="";


	try{
		//钱箱清点，不需要考虑长周期，按统计日期计算
		/*
		  车站--钱箱实际卸载后  更新钱箱清点记录 和审核表的实际卸载金额
		  接口 -  获取接口金额后更新钱箱系统金额
		  入账日期：--- 更新入账字段，并更新对应入账日期的车站营收日报 20号

		  地铁日报表  -现金  营收日报TVM金额
		  
		  车站营收日报 钱箱清点金额计算公式
		  
		  ////----------------------当日实际清点金额合计  - 未卸载日期为当日  且实际卸载日期不为空，入账日期(为空或者等于未卸载日期)  不管  
		  
		  当未卸载日期不等于入账日期时考虑
		  
		  未卸载日期为当日(10日)  且实际卸载日期不为空（11日）  且未卸载日期！= 入账日期（20日）时需要考虑
		  1. 10号的清点数据删除实际卸载金额
		  2. 20号的清点数据加上实际卸载金额

		  

		  当生成10号的日报时，统计日不等于入账日 判断入账日期不为空 且 ！=未卸载日期10日 统计日时 并且2024-10-10<2024-10-20 减去
		  当生成20号的日报时，统计日等于入账日， 且未卸载日期不等于入账日期时，并且 2024-10-20>2024-10-10 ，20日加上钱箱实际未卸载金额

		  审核设置入账时间时，调用两次，第一次日期传20日，第二次日期传10日

		  select UNINSTALLED_DATE as '未卸载日期', UNINSTALL_DATE as '实际卸载日期' , POST_DATE as '入账日期' from sw_st_input_cashbox_uninstalled where LINE_CODE='2'  and station_code='41' and DEL_FLAG='0'
           and POST_DATE is not null and UNINSTALL_AMOUNT!=0

		   String sqlUninstallPre = "select UNINSTALLED_DATE as '未卸载日期', UNINSTALL_DATE as '实际卸载日期' , POST_DATE as '入账日期',UNINSTALL_AMOUNT as '实际卸载金额' from sw_st_input_cashbox_uninstalled where LINE_CODE='2'  and station_code='41' and DEL_FLAG='0'"
                                 +" and POST_DATE is not null and UNINSTALL_AMOUNT!=0"
                                 +" and UNINSTALLED_DATE='"+statisticsDate+"' and POST_DATE>'"+statisticsDate+"'";

		
		*/
       
		


		String sqlCashBox ="select sum(ifnull(check_amount,0)) as amount from sw_st_tvm_check_detail where line_code='"+lineCode+"' and station_code='"+stationCode+"' and revenue_time='"+statisticsDate+"' and del_flag='0' ";
        
        List<Map<String, Object>> listCashBox = JdbcUtil.exeSqlQueryByCon(sqlCashBox);
        if(listCashBox.size()>0){
        	bigCashBox = (BigDecimal)(listCashBox.get(0).get("amount"));
            if(bigCashBox ==null){
                bigCashBox= new BigDecimal("0.00");
            }
        }
		 //钱箱未卸载 统计10日数据时
		 /*
		String sqlUninstallPre = "select sum(UNINSTALL_AMOUNT) as amount from sw_st_input_cashbox_uninstalled where LINE_CODE='"+lineCode+"'  and station_code='"+stationCode+"' and DEL_FLAG='0'"
                                 +" and POST_DATE is not null and UNINSTALL_AMOUNT!=0"
                                 +" and UNINSTALLED_DATE='"+statisticsDate+"' and POST_DATE>'"+statisticsDate+"'";
		List<Map<String, Object>> listCashBoxInstallPre = JdbcUtil.exeSqlQueryByCon(sqlUninstallPre);
        if(listCashBoxInstallPre.size()>0){
			BigDecimal bigCashBoxUnPre = new BigDecimal("0.00");
        	bigCashBoxUnPre = (BigDecimal)(listCashBoxInstallPre.get(0).get("amount"));
            if(bigCashBoxUnPre !=null){
                bigCashBox.subtract(bigCashBoxUnPre);
            }
        }
		 //钱箱未卸载 统计20日数据时
		String sqlUninstallLast = "select sum(UNINSTALL_AMOUNT) as amount from sw_st_input_cashbox_uninstalled where LINE_CODE='2'  and station_code='41' and DEL_FLAG='0'"
                                 +" and POST_DATE is not null and UNINSTALL_AMOUNT!=0"
                                 +" and UNINSTALLED_DATE<'"+statisticsDate+"' and POST_DATE='"+statisticsDate+"'";
		List<Map<String, Object>> listCashBoxInstallLast = JdbcUtil.exeSqlQueryByCon(sqlUninstallLast);
        if(listCashBoxInstallLast.size()>0){
			BigDecimal bigCashBoxUnLast = new BigDecimal("0.00");
        	bigCashBoxUnLast = (BigDecimal)(listCashBoxInstallLast.get(0).get("amount"));
            if(bigCashBoxUnLast !=null){
                bigCashBox.add(bigCashBoxUnLast);
            }
        }*/
       //补币金额---返回负数，暂时不需要考虑长周期  开始日期和结束日期        
       
        String sqlSupplment = " select  0-sum(ifnull(total_supplement_amount,0)) as amount from  sw_st_tvm_supplement_currency  where  line_code='"+lineCode+"' and station_code='"+stationCode+"' and del_flag='0' and supplement_currency_time>='"+beginDate+"' and supplement_currency_time<='"+endDate+"'";
        List<Map<String, Object>> listSupplment = JdbcUtil.exeSqlQueryByCon(sqlSupplment);
        if(listSupplment!=null && listSupplment.size()>0){
        	bigSupplment = (BigDecimal)(listSupplment.get(0).get("amount"));
            if(bigSupplment == null){
                bigSupplment = new BigDecimal("0.00");
            }
        }
        //手工清出  不需要考虑长周期，开始日期和结束日期       
        String sqlHand = "select sum(ifnull(total_clean_amount,0)) as amount  from sw_st_handwork_clean_record where line_code='"+lineCode+"' and station_code='"+stationCode+"' and del_flag='0' and clean_time>='"+beginDate+"' and clean_time<='"+endDate+"'";
        List<Map<String, Object>> listHand = JdbcUtil.exeSqlQueryByCon(sqlHand);
        if(listHand!=null && listHand.size()>0){
        	bigHand = (BigDecimal)(listHand.get(0).get("amount"));
            if(bigHand == null){
                bigHand = new BigDecimal("0.00");
            }
        }    
          
        //营销车票现金部分，不需要考虑长周期
       
        String marketGroupSql = "select CONVERT(sum(ifnull(discount_amount,0)),decimal(12,2)) as amount from sw_st_marketing_ticket_group_sell a,SW_ST_MARKETING_TICKET b where a.relation_id=b.id and a.del_flag='0' and b.del_flag='0' and b.LINE_CODE='"+lineCode+"' and b.STATION_CODE='"+stationCode+"' and b.OPERTOR_TIME='"+statisticsDate+"' and a.pay_method='0'";
        List<Map<String, Object>> listMarketGroup= JdbcUtil.exeSqlQueryByCon(marketGroupSql);
        if(listMarketGroup!=null && listMarketGroup.size()>0){
        	bigMarketGroup = (BigDecimal)(listMarketGroup.get(0).get("amount"));
            if(bigMarketGroup == null){
                bigMarketGroup =  new BigDecimal("0.00");
            }
        }
        
       
        String marketConductorSellSql = "select CONVERT(sum(ifnull(CASH_SELL_AMOUNT,0)),decimal(12,2)) as amount  from sw_st_marketing_ticket_offine_sell a,SW_ST_MARKETING_TICKET b where a.relation_id=b.id and a.del_flag='0' and b.del_flag='0' and b.LINE_CODE='"+lineCode+"' and b.STATION_CODE='"+stationCode+"' and b.OPERTOR_TIME='"+statisticsDate+"'  ";
        List<Map<String, Object>> listMarketCondutctorSell= JdbcUtil.exeSqlQueryByCon(marketConductorSellSql);
        if(listMarketCondutctorSell!=null && listMarketCondutctorSell.size()>0){
        	bigMarketCondutctorSell = (BigDecimal)(listMarketCondutctorSell.get(0).get("amount"));
            if(bigMarketCondutctorSell == null){
                bigMarketCondutctorSell =  new BigDecimal("0.00");
            }
        }
               
       //预制单程票，不需要考虑长周期
        StringBuilder sbYZDCP = new StringBuilder();
        sbYZDCP.append("select ")
		//.append("  CONVERT(ifnull(sum(ifnull(a.WINDOWS_NUM - a.WINDOWS_CLOSE_NUM - a.INVALID_TICKET_NUM,0)*CONVERT(A.TICKET_VALUE, SIGNED)*100),0),SIGNED) amount")
        .append("  CONVERT(ifnull(sum(ifnull(a.WINDOWS_NUM - a.WINDOWS_CLOSE_NUM - a.INVALID_TICKET_NUM - a.POS_SALE_NUM ,0)*CONVERT(A.TICKET_VALUE, decimal(12,2))),0),decimal(12,2)) amount")
		.append(" from sw_st_ticket_petty_add_details a, sw_st_ticket_petty_add b ")
		.append(" where a.RELATION_ID =b.id and a.DEL_FLAG='0' and  b.DEL_FLAG='0' and b.LINE_CODE='"+lineCode+"' and b.STATION_CODE='"+stationCode+"' and b.WORKING_START_TIME >= '"+beginDate+"' and b.WORKING_END_TIME <= '"+endDate+"'")
		.append(" and a.TYPE='2'  and a.ticket_name='YZDCP'");
        
       
        List<Map<String, Object>> listYZDCP= JdbcUtil.exeSqlQueryByCon(sbYZDCP.toString());
        if(listYZDCP!=null && listYZDCP.size()>0){
        	bigYZDCP = ObjectValueUtils.getBigDecimal(listYZDCP.get(0).get("amount"));
            if(bigYZDCP == null){
                bigYZDCP =  new BigDecimal("0.00");
            }
        }

        //应急纸票 不需要考虑长周期
        StringBuilder sbYJZP = new StringBuilder();
        sbYJZP.append("select ")
        	    //.append("  CONVERT(ifnull(sum(ifnull(a.WINDOWS_NUM - a.WINDOWS_CLOSE_NUM - a.INVALID_TICKET_NUM,0)*CONVERT(A.TICKET_VALUE, SIGNED)*100),0),SIGNED) amount")
                .append("  CONVERT(ifnull(sum(ifnull(a.WINDOWS_NUM - a.WINDOWS_CLOSE_NUM -a.INVALID_TICKET_NUM - a.POS_SALE_NUM,0)*CONVERT(A.TICKET_VALUE, decimal(12,2))),0),decimal(12,2)) amount")
                .append(" from sw_st_ticket_petty_add_details a, sw_st_ticket_petty_add b ")
                .append(" where a.RELATION_ID =b.id and a.DEL_FLAG='0' and  b.DEL_FLAG='0' and b.LINE_CODE='"+lineCode+"' and b.STATION_CODE='"+stationCode+"' and b.WORKING_START_TIME >= '"+beginDate+"' and b.WORKING_END_TIME <= '"+endDate+"'")
                .append(" and a.TYPE='2'  and a.ticket_name='YJZP'");
       
        List<Map<String, Object>> listYJZP= JdbcUtil.exeSqlQueryByCon(sbYJZP.toString());
        if(listYJZP!=null && listYJZP.size()>0){
        	bigYJZP =ObjectValueUtils.getBigDecimal(listYJZP.get(0).get("amount"));
            if(bigYJZP == null){
                bigYJZP =  new BigDecimal("0.00");
            }
        }

		//计算乘客事务即时退换票中预制单程票和应急纸票的对应金额，然后相关的做减法
		 String sqlTicketOtherYZDCP = "select ifnull(sum(a.TICKET_PRICE),0) as amount from sw_st_input_passenger_work_ticket a\n"+
						"where  a.OPERATION_TYPE='0'\n"+
						"and a.REPLACE_TICKET_TYPE='YZDCP'\n"+
						"and a.DEL_FLAG='0'\n"+
						"and a.LINE_CODE='"+lineCode+"'\n"+
						"and a.STATION_CODE='"+stationCode+"'\n"+
						"and a.HANDLE_TYPE='1'\n"+
						"and DATE_FORMAT(a.HANDLE_TIME,'%Y-%m-%d') = '"+statisticsDate+"'\n"+
						"and a.CONFIRM_STATUS='1'\n";
        List<Map<String, Object>> listOtherYZDCP= JdbcUtil.exeSqlQueryByCon(sqlTicketOtherYZDCP);
        if(listOtherYZDCP!=null && listOtherYZDCP.size()>0){
        	bigOtherYZDCP = ObjectValueUtils.getBigDecimal(listOtherYZDCP.get(0).get("amount"));
            if(bigOtherYZDCP == null){
                bigOtherYZDCP =  new BigDecimal("0.00");
            }
        }

		/*

		 String sqlTicketOtherYJZP = "select ifnull(sum(a.TICKET_PRICE),0) as amount from sw_st_input_passenger_work_ticket a\n"+
						"where  a.OPERATION_TYPE='0'\n"+
						"and a.REPLACE_TICKET_TYPE='YJZP'\n"+
						"and a.DEL_FLAG='0'\n"+
						"and a.LINE_CODE='"+lineCode+"'\n"+
						"and a.STATION_CODE='"+stationCode+"'\n"+
						"and a.HANDLE_TYPE='1'\n"+
						"and a.HANDLE_TIME between '"+beginDate+"' and '"+endDate+"'\n"+
						"and a.CONFIRM_STATUS='1'\n";
		*/
		String sqlTicketOtherYJZP = "select ifnull(SUM(ABS(a.AMOUNT)),0) as amount from sw_st_input_passenger_work_other a\n"+
						"where  a.DATA_TYPE='1'\n"+
						"and a.HAPPEN_CAUSE='1'\n"+
						"and a.DEL_FLAG='0'\n"+
						"and a.LINE_CODE='"+lineCode+"'\n"+
						"and a.STATION_CODE='"+stationCode+"'\n"+					
						"and a.INPUT_TIME between '"+beginDate+"' and '"+endDate+"'\n"+
						"and a.CONFIRM_STATUS='1'\n";
        List<Map<String, Object>> listOtherYJZP= JdbcUtil.exeSqlQueryByCon(sqlTicketOtherYJZP);
        if(listOtherYJZP!=null && listOtherYJZP.size()>0){
        	bigOtherYJZP = ObjectValueUtils.getBigDecimal(listOtherYJZP.get(0).get("amount"));
            if(bigOtherYJZP == null){
                bigOtherYJZP =  new BigDecimal("0.00");
            }
        }

		//售票员实收金额
          
        StringBuilder sbConductorRealAmount = new StringBuilder();
        sbConductorRealAmount.append("select ")
        	  .append("  CONVERT(ifnull(sum(ifnull(a.real_amount,0)),0),decimal(12,2))  amount")
			  .append( " from sw_st_conductor_settle a where  a.DEL_FLAG='0'  and a.LINE_CODE='"+lineCode+"' and a.STATION_CODE='"+stationCode+"' and settle_time='"+statisticsDate+"'");              
        
		List<Map<String, Object>> listConductorRealAmount = JdbcUtil.exeSqlQueryByCon(sbConductorRealAmount.toString());
        if(listConductorRealAmount!=null && listConductorRealAmount.size()>0){
        	bigConductorRealAmount =ObjectValueUtils.getBigDecimal(listConductorRealAmount.get(0).get("amount"));
            if(bigConductorRealAmount == null){
                bigConductorRealAmount = new BigDecimal("0.00");
            }
        }
        //补售票员短款，不需要考虑长周期
        String sqlConductorShort = "select convert(sum(ifnull(short_amount,0))/100,decimal(16,2)) as amount from sw_au_conductor_short  where del_flag='0' and line_code='"+lineCode+"' and station_code='"+stationCode+"' and DATE_FORMAT(SUP_PAYMENT_TIME,'%Y-%m-%d') = '"+statisticsDate+"'";
        
        List<Map<String, Object>> listConductorShort= JdbcUtil.exeSqlQueryByCon(sqlConductorShort);
        if(listConductorShort!=null && listConductorShort.size()>0){
        	bigShort = (BigDecimal)(listConductorShort.get(0).get("amount"));
            if(bigShort == null){
                bigShort =  new BigDecimal("0.00");
            }
        }
        //加收票款  售票员上交预收款数据录入  不需要考虑长周期
		/*
        String  sqlAdd = "select  sum(ifnull(ADAVNCE_PAYMENT,0)) as amount  from  sw_st_conductor_advance_payment where del_flag='0' and line_code='"+lineCode+"' and station_code='"+stationCode+"' and HAND_TIME='"+statisticsDate+"'";

         List<Map<String, Object>> listAdd= JdbcUtil.exeSqlQueryByCon(sqlAdd);
        if(listAdd!=null && listAdd.size()>0){
        	bigAdd = (BigDecimal)(listAdd.get(0).get("amount"));
            if(bigAdd == null){
                bigAdd =  new BigDecimal("0.00");
            }
        }
		*/
		//钱箱未卸载金额
	    String  sqlCashBoxUnload = "select  sum(ifnull(UNINSTALL_AMOUNT,0)) as amount  from  sw_st_input_cashbox_uninstalled where del_flag='0' and  IS_SUBMIT='1' and line_code='"+lineCode+"' and station_code='"+stationCode+"' and UNINSTALL_DATE='"+statisticsDate+"'";

         List<Map<String, Object>> listCashBoxUnload= JdbcUtil.exeSqlQueryByCon(sqlCashBoxUnload);
        if(listCashBoxUnload!=null && listCashBoxUnload.size()>0){
        	bigCashBoxUnload = (BigDecimal)(listCashBoxUnload.get(0).get("amount"));
            if(bigCashBoxUnload == null){
                bigCashBoxUnload =  new BigDecimal("0.00");
            }
        }
        //客值交接长款和其他  不需要考虑长周期
        String conductorLongAmount = "select sum(ifnull(OTHER_MONEY,0)) as other,sum(ifnull(CUSTOMER_HANDOVER_LONG,0)) as handover,REMARK as remark from sw_st_other_fare where line_code='"+lineCode+"' and station_code='"+stationCode+"' and ENTRY_DATE='"+statisticsDate+"' ";
       
        List<Map<String, Object>> listConductorLong= JdbcUtil.exeSqlQueryByCon(conductorLongAmount);
        if(listConductorLong!=null && listConductorLong.size()>0){
        	bigLong = (BigDecimal)(listConductorLong.get(0).get("handover"));
            if(bigLong == null){
                bigLong =  new BigDecimal("0.00");
            }
        	bigOther = (BigDecimal)(listConductorLong.get(0).get("other"));
            if(bigOther == null){
                bigOther = new BigDecimal("0.00");
            }
			otherRemark =  (String)(listConductorLong.get(0).get("remark"));
			if(otherRemark == null){
                otherRemark ="";
            }

        }
       
        
        //获取客值人员名称、客值人员工号、值班站长名称、值班站长工号
        //车站营收日报表组织客值人员时只查创建时间7点前的获取
       
        String sqlConductor = "select PASSENGER_ATTENDANT_NAME as name1,PASSENGER_ATTENDANT_JOB_NO as no1 ,ATTENDANCE_PERSON_JOB_NO as no2,ATTENDANCE_PERSON_NAME as name2 from sw_st_bom_income_settle where del_flag = '0' and line_code='"+lineCode+"' and station_code='"+stationCode+"' and record_Time='"+statisticsDate+"' and DATE_FORMAT(create_time, '%H') < 7 order by create_time desc limit 0,1";
        List<Map<String, Object>> listConductor= JdbcUtil.exeSqlQueryByCon(sqlConductor);		
        if(listConductor!=null && listConductor.size()>0){			
			Map map = listConductor.get(0);
        	name1 = String.valueOf(map.get("name1"));
        	no1 =  String.valueOf(map.get("no1"));			
        	name2 = String.valueOf(map.get("name2"));
        	no2 =  String.valueOf(map.get("no2"));
			
        }
		
		}catch(Exception e){
		     
		}
		 //TVM小計
        BigDecimal subTvmTotal = bigCashBox.add(bigSupplment).add(bigHand);
		 //BOM实收小計
        BigDecimal subBomTotal =bigConductorRealAmount;//bigMarketGroup.add(bigMarketCondutctorSell).add(bigYZDCP).add(bigYJZP).add(bigConductorRealAmount); BOM实收总计实际就是售票员结算单的实收金额
       //营收总金额
        BigDecimal total = subTvmTotal.add(subBomTotal).add(bigShort).add(bigLong).add(bigOther).add(bigCashBoxUnload);
       
        SwStReportqueryStationincome income = new SwStReportqueryStationincome(); 
        income.setLineCode(lineCode);
        income.setStationCode(stationCode);
        income.setStatisticsDate(DateUtilStm.stringTolocalDate(statisticsDate,"yyyy-MM-dd"));
        income.setCashBox(bigCashBox);
        income.setSupplment(bigSupplment);
        income.setHand(bigHand);
        income.setSubTvmTotal(subTvmTotal);
        income.setMarketTicket(bigMarketGroup.add(bigMarketCondutctorSell));
        income.setYzdcp(bigYZDCP.subtract(bigOtherYZDCP));
        income.setYjzp(bigYJZP.subtract(bigOtherYJZP));
		income.setConductorRealAmount(bigConductorRealAmount);
        income.setSubBomTotal(subBomTotal);
        income.setConductorShort(bigShort);
        income.setAddMoney(bigAdd);
        income.setConductorLong(bigLong);
        income.setOther(bigOther);
        income.setTotal(total);
        income.setPassengerAttendName(name1);//客运值班员    
        income.setPassengerAttendJobNo(no1);//客运值班员工号
        income.setAttendName(name2);//复核人
        income.setAttendJobNo(no2);//复核人工号
		income.setOtherRemark(otherRemark);
        
        income.setCreateTime(LocalDateTime.now());
        income.setDelFlag("0");
        
      
        /*
        Map map = new HashMap();
        
        map.put("statisticsDate", statisticsDate);
        
        //TVM收入
        map.put("cashBox", bigCashBox);  //钱箱票款
        map.put("supplment", bigSupplment);//补币金额
        map.put("hand", bigHand);   //手工清出     
        map.put("subTvmTotal",subTvmTotal );
        
        map.put("boxDiff", 0.00);//钱箱差额
        
        //BOM收入
        map.put("marketTicket",bigMarketGroup.add(bigMarketCondutctorSell));//营销车票
        map.put("dayTicket", 0.00);//日票
        map.put("yzdcp",bigYZDCP);//预制单程票
        map.put("yjzp",bigYJZP);   //应急纸票
        
        map.put("subBomTotal", subBomTotal);//BOM实收总金额小计
        
        //其他内容
        map.put("conductorShort",bigShort);//补售票员短款
        map.put("addMoney", bigAdd); //加收票款
        map.put("conductorLong",bigLong);//客值交接长款
        map.put("other",bigOther);//其他
        
        map.put("total", total);//营收总金额
        map.put("remark",""); //备注
        map.put("passengerAttendName", name1);//客运值班员    
        map.put("passengerAttendJobNo", no1);//客运值班员工号
        map.put("attendName", name2);//复核人
        map.put("attendNameJobNo", no2);//复核人工号   
        */
       
        return income;
		
	}

    /*
     * 营收日报表查询
     */
    public static SwStReportqueryStationincome  getStationIncomeReport(SwStReportqueryStationincome oldServiceOne) {
        String lineCode = oldServiceOne.getLineCode();
        String stationCode = oldServiceOne.getStationCode();
        String statisticsDate = DateUtilStm.LocalDateTrunString(oldServiceOne.getStatisticsDate());
        BigDecimal oldBigCashBox = oldServiceOne.getCashBox();
        BigDecimal oldTotal = oldServiceOne.getTotal();
        //钱箱清点总金额
        BigDecimal bigCashBox = new BigDecimal("0.00");
        try{
            //钱箱清点，不需要考虑长周期，按统计日期计算
            String sqlCashBox ="select sum(ifnull(check_amount,0)) as amount from sw_st_tvm_check_detail where line_code='"+lineCode+"' and station_code='"+stationCode+"' and revenue_time='"+statisticsDate+"' and del_flag='0' ";
            List<Map<String, Object>> listCashBox = JdbcUtil.exeSqlQueryByCon(sqlCashBox);
            if(listCashBox.size()>0){
                bigCashBox = (BigDecimal)(listCashBox.get(0).get("amount"));
                if(bigCashBox ==null){
                    bigCashBox= new BigDecimal("0.00");
                }
            }
        }catch(Exception e){

        }
        if(oldTotal==null){
            oldTotal = new BigDecimal("0.00");
        }
        if(oldBigCashBox==null){
            oldBigCashBox = new BigDecimal("0.00");
        }
        //营收总金额
        BigDecimal total = oldTotal.subtract(oldBigCashBox).add(bigCashBox);
        SwStReportqueryStationincome income = new SwStReportqueryStationincome();
        income.setCashBox(bigCashBox);
        income.setTotal(total);
        return income;
    }
	
	public static List<SwStReportqueryStationincome> calcStationIncomeDailyReport(String dateTime){
		
		String sql = "select a.line_code as linecode,a.station_code as stationcode,a.begin_time as begintime,a.end_time as endtime,a.submit_time as submittime,"
				+" ifnull(b.settlement_term,1) as settlementterm,a.id  from sw_ck_data_submit a left join sw_pm_long_period_settlement b on a.line_code=b.line_code and b.DEL_FLAG='0' and b.STATUS='1'"
				+" and a.station_code=b.station_code  order by a.line_code,a.station_code";
		List<SwStReportqueryStationincome> lstReturn = new ArrayList<SwStReportqueryStationincome> ();
		try {
			List<Map<String, Object>> listStations= JdbcUtil.exeSqlQueryByCon(sql);
			if(listStations!=null && listStations.size()>0){
			
				for(int i=0;i<listStations.size();i++){
					Map<String,Object> map = listStations.get(i);
					String lineCode = (String)map.get("linecode");
					String stationCode = (String)map.get("stationcode");

                    Timestamp timestampBeginTime = (Timestamp) map.get("begintime");
                    String beginTime = DateUtilStm.LocalDateTimeToString(timestampBeginTime.toLocalDateTime());

                    Timestamp timestampEndTime = (Timestamp) map.get("endtime");
                    String endTime = DateUtilStm.LocalDateTimeToString(timestampEndTime.toLocalDateTime());

                    Timestamp timestampSubmitTime = (Timestamp) map.get("submittime");
                    String submitTime = DateUtilStm.LocalDateTimeToString(timestampSubmitTime.toLocalDateTime());

					long relationId =(Long)map.get("id");
					int settlementTerm = new Long((long)map.get("settlementterm")).intValue();
					Map<String,Object> param = new HashMap<String,Object>();
					
					param.put("lineCode", lineCode);
					param.put("stationCode", stationCode);
					param.put("statisticsDate", DateUtilStm.stringToDate(dateTime, "yyyy-MM-dd"));
					param.put("beginTime", beginTime);
					param.put("endTime", endTime);
					param.put("submitTime", submitTime);
					param.put("settlementTerm", settlementTerm);
					
					SwStReportqueryStationincome income =  getStationIncomeReport(param);
					income.setRelationId(relationId);
					income.setBeginTime(DateUtilStm.stringTolocalDate(beginTime));
					income.setEndTime(DateUtilStm.stringTolocalDate(endTime));
					income.setSubmitTime(DateUtilStm.stringTolocalDate(submitTime));
					income.setSettlementTerm(settlementTerm);
					lstReturn.add(income);			 
					
				}
				
				
			}
		} catch (SQLException  e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return lstReturn;
		
	}

    public static List<SwStReportqueryStationincome> incomeDailyReport(String lineCodes,String dateTime){
        String sql  = "select id,line_code as linecode,code as stationcode from sw_param_station where line_code='"+lineCodes+"' ";
		/*
		String sql = "select a.line_code as linecode,a.station_code as stationcode,a.begin_time as begintime,a.end_time as endtime,a.submit_time as submittime,"
                +" ifnull(b.settlement_term,1) as settlementterm,a.id  from sw_ck_data_submit a left join sw_pm_long_period_settlement b on a.line_code=b.line_code "
                +" and a.station_code=b.station_code where a.line_code = '"+lineCodes+ "' AND '"+dateTime+"' BETWEEN BEGIN_TIME AND end_time "+
                " order by a.line_code,a.station_code";
        String sqls = "select a.line_code as linecode,a.station_code as stationcode,a.begin_time as begintime,a.end_time as endtime,a.submit_time as submittime,"
                +" ifnull(b.settlement_term,1) as settlementterm,a.id  from sw_ck_data_submit_log a left join sw_pm_long_period_settlement b on a.line_code=b.line_code "
                +" and a.station_code=b.station_code where a.line_code = '"+lineCodes+ "' AND '"+dateTime+"' BETWEEN BEGIN_TIME AND end_time "+
                " order by a.line_code,a.station_code";
        */
		List<SwStReportqueryStationincome> lstReturn = new ArrayList<SwStReportqueryStationincome> ();
        try {
            List<Map<String, Object>> listStations = JdbcUtil.exeSqlQueryByCon(sql);
            //List<Map<String, Object>> listStationsLog = JdbcUtil.exeSqlQueryByCon(sqls);
            if(listStations!=null && listStations.size()>0){
                for(int i=0;i<listStations.size();i++){
                    Map<String,Object> map = listStations.get(i);
                    String lineCode = (String)map.get("linecode");
                    String stationCode = (String)map.get("stationcode");
                    //Timestamp timestampBeginTime = (Timestamp) map.get("begintime");
                    String beginTime = "";//DateUtilStm.LocalDateTimeToString(timestampBeginTime.toLocalDateTime());
                    //Timestamp timestampEndTime = (Timestamp) map.get("endtime");
                    String endTime = "";//DateUtilStm.LocalDateTimeToString(timestampEndTime.toLocalDateTime());
                    //Timestamp timestampSubmitTime = (Timestamp) map.get("submittime");
                    String submitTime = "";//DateUtilStm.LocalDateTimeToString(timestampSubmitTime.toLocalDateTime());                  
					long relationId =0L;//(Long)map.get("id");
                    int settlementTerm = 1;					
                    Map<String,Object> param = new HashMap<String,Object>();
                    param.put("lineCode", lineCode);
                    param.put("stationCode", stationCode);
                    param.put("statisticsDate", DateUtilStm.stringToDate(dateTime, "yyyy-MM-dd"));
                    param.put("beginTime", beginTime);
                    param.put("endTime", endTime);
                    param.put("submitTime", submitTime);
                    param.put("settlementTerm", settlementTerm);
                    SwStReportqueryStationincome income =  getStationIncomeReport(param);
                    income.setRelationId(relationId);
                    income.setLineCode(lineCode);
                    income.setStationCode(stationCode);
                    income.setBeginTime(DateUtilStm.stringTolocalDate(dateTime,"yyyy-MM-dd"));
                    income.setEndTime(DateUtilStm.stringTolocalDate(dateTime,"yyyy-MM-dd"));
                    income.setSubmitTime(DateUtilStm.stringTolocalDate(dateTime,"yyyy-MM-dd"));
                    income.setSettlementTerm(settlementTerm);
                    lstReturn.add(income);
                }
            }
			/*
            if(listStationsLog!=null && listStationsLog.size()>0) {
                for (int i = 0; i < listStationsLog.size(); i++) {
                    Map<String, Object> map = listStationsLog.get(i);
                    String lineCode = (String) map.get("linecode");
                    String stationCode = (String) map.get("stationcode");
                    Timestamp timestampBeginTime = (Timestamp) map.get("begintime");
                    String beginTime = DateUtilStm.LocalDateTimeToString(timestampBeginTime.toLocalDateTime());
                    Timestamp timestampEndTime = (Timestamp) map.get("endtime");
                    String endTime = DateUtilStm.LocalDateTimeToString(timestampEndTime.toLocalDateTime());
                    Timestamp timestampSubmitTime = (Timestamp) map.get("submittime");
                    String submitTime = DateUtilStm.LocalDateTimeToString(timestampSubmitTime.toLocalDateTime());
                    long relationId = (Long) map.get("id");
                    int settlementTerm = new Long((long) map.get("settlementterm")).intValue();
                    Map<String, Object> param = new HashMap<String, Object>();
                    param.put("lineCode", lineCode);
                    param.put("stationCode", stationCode);
                    param.put("statisticsDate", DateUtilStm.stringToDate(dateTime, "yyyy-MM-dd"));
                    param.put("beginTime", beginTime);
                    param.put("endTime", endTime);
                    param.put("submitTime", submitTime);
                    param.put("settlementTerm", settlementTerm);
                    SwStReportqueryStationincome income = getStationIncomeReport(param);
                    income.setRelationId(relationId);
                    income.setLineCode(lineCode);
                    income.setStationCode(stationCode);
                    income.setBeginTime(DateUtilStm.stringTolocalDate(beginTime));
                    income.setEndTime(DateUtilStm.stringTolocalDate(endTime));
                    income.setSubmitTime(DateUtilStm.stringTolocalDate(submitTime));
                    income.setSettlementTerm(settlementTerm);
                    lstReturn.add(income);
                }
            }*/
        } catch (SQLException  e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return lstReturn;
    }


    public static List<SwStReportqueryStationincome> calcStationIncomeDailyReport(String queryLineCode,String queryStationCode,String dateTime){

        String sql = "select a.line_code as linecode,a.station_code as stationcode,a.begin_time as begintime,a.end_time as endtime,a.submit_time as submittime,"
                +" ifnull(b.settlement_term,1) as settlementterm,a.id  from sw_ck_data_submit a left join sw_pm_long_period_settlement b on a.line_code=b.line_code and b.DEL_FLAG='0' and b.STATUS='1' "
                +" and a.station_code=b.station_code where a.line_code ='"+queryLineCode+"' and a.station_code ='"+queryStationCode+"' order by a.line_code,a.station_code";
        List<SwStReportqueryStationincome> lstReturn = new ArrayList<SwStReportqueryStationincome> ();
        try {
            List<Map<String, Object>> listStations= JdbcUtil.exeSqlQueryByCon(sql);
            if(listStations!=null && listStations.size()>0){

                for(int i=0;i<listStations.size();i++){
                    Map<String,Object> map = listStations.get(i);
                    String lineCode = (String)map.get("linecode");
                    String stationCode = (String)map.get("stationcode");

                    Timestamp timestampBeginTime = (Timestamp) map.get("begintime");
                    String beginTime = DateUtilStm.LocalDateTimeToString(timestampBeginTime.toLocalDateTime());

                    Timestamp timestampEndTime = (Timestamp) map.get("endtime");
                    String endTime = DateUtilStm.LocalDateTimeToString(timestampEndTime.toLocalDateTime());

                    Timestamp timestampSubmitTime = (Timestamp) map.get("submittime");
                    String submitTime = DateUtilStm.LocalDateTimeToString(timestampSubmitTime.toLocalDateTime());

                    long relationId =(Long)map.get("id");
                    int settlementTerm = new Long((long)map.get("settlementterm")).intValue();
                    Map<String,Object> param = new HashMap<String,Object>();

                    param.put("lineCode", lineCode);
                    param.put("stationCode", stationCode);
                    param.put("statisticsDate", DateUtilStm.stringToDate(dateTime, "yyyy-MM-dd"));
                    param.put("beginTime", beginTime);
                    param.put("endTime", endTime);
                    param.put("submitTime", submitTime);
                    param.put("settlementTerm", settlementTerm);

                    SwStReportqueryStationincome income =  getStationIncomeReport(param);
                    income.setRelationId(relationId);
                    income.setBeginTime(DateUtilStm.stringTolocalDate(beginTime));
                    income.setEndTime(DateUtilStm.stringTolocalDate(endTime));
                    income.setSubmitTime(DateUtilStm.stringTolocalDate(submitTime));
                    income.setSettlementTerm(settlementTerm);
                    lstReturn.add(income);

                }

            }
        } catch (SQLException  e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

        return lstReturn;

    }

    /**
	 * 根据收益日期获取补币的TVM编号和钱箱编号
	 * @param lineCode
	 * @param stationCode
	 * @param tvmCashBoxType
	 * @param revenueTime
	 * @param tvmCode
	 * @param flag  1:正常新增或者修改    2：报表差错的新增或者修改   
	 * @return
	 */
    
	public static List<Map<String, Object>> getSupplmentInfo(String lineCode,String stationCode,String tvmCashBoxType,String revenueTime,String tvmCode,String flag){
		
	   String sql = "select distinct a.tvm_code as tvmcode,a.tvm_cash_box_num as cashboxcode from view_sw_st_tvm_supplement_currency a "
					+" join sw_ck_data_submit s on s.line_code='"+lineCode+"' and s.station_code='"+stationCode+"'  "
					+" left join sw_st_longperiod_settle_log c on c.device_type='1' and c.line_code='"+lineCode+"' and c.station_code='"+stationCode+"' and a.tvm_code = c.DEVICE_NO  "
					+" where a.line_code='"+lineCode+"' and a.station_code='"+stationCode+"' and a.tvm_cash_box_type='"+tvmCashBoxType+"' "
					+" and a.supplement_currency_time > s.END_TIME "
					+" and a.supplement_currency_time between date_add(ifnull(c.END_SETTLE_DATE,'2020-01-01'),interval 1 second) and date_add('"+revenueTime+"',interval 93599 second) ";
	   String sqlFlag = "select count(1) as amount from view_sw_st_tvm_supplement_currency a "
                    +" join sw_ck_data_submit s on s.line_code='"+lineCode+"' and s.station_code='"+stationCode+"'  "
                    +" left join sw_st_longperiod_settle_log c on c.device_type='1' and c.line_code='"+lineCode+"' and c.station_code='"+stationCode+"' and a.tvm_code = c.DEVICE_NO  "
                    +" where a.line_code='"+lineCode+"' and a.station_code='"+stationCode+"' and a.tvm_cash_box_type='"+tvmCashBoxType+"' "
                    +" and a.supplement_currency_time > s.END_TIME "
                    +" and a.supplement_currency_time between date_add(ifnull(c.END_SETTLE_DATE,'2020-01-01'),interval 1 second) and date_add('"+revenueTime+"',interval 93599 second) ";
        boolean isRepair = false;
		if("2".equals(flag)){
          isRepair = true;
		   sql = "select distinct a.tvm_code as tvmcode,a.tvm_cash_box_num as cashboxcode from view_sw_st_tvm_supplement_currency a "
                    +" left join sw_st_tvm_income_settle c on c.del_flag='0' and a.tvm_code = c.tvm_code   and c.line_code='"+lineCode+"' and c.station_code='"+stationCode+"' "
                    +" where a.line_code='"+lineCode+"' and a.station_code='"+stationCode+"' and a.tvm_cash_box_type='"+tvmCashBoxType+"' "
                    +" and date_add('"+revenueTime+"',interval 93599 second) between c.SETTLE_START_TIME and c.SETTLE_END_TIME "
                    +" and a.supplement_currency_time between c.SETTLE_START_TIME and c.SETTLE_END_TIME ";
		}
         List<Map<String, Object>> lstSupplement = new ArrayList<Map<String, Object>>();
		try{
		    if(!isRepair){  //非报表修改状态，如果是补录的，需要根据ck_data_submit_log表关联查找补币
                List<Map<String, Object>> listFlag = JdbcUtil.exeSqlQueryByCon(sqlFlag);
				//log.info("-------------查询补录的SQL===验证=="+sqlValidate );
				//log.info("-------------查询补录的SQL===验证= list="+listValidate.size());
                if(listFlag==null || listFlag.size()==0 || "0".equals(String.valueOf(listFlag.get(0).get("amount")))) {//说明是补录的
                    sql = "select distinct a.tvm_code as tvmcode,a.tvm_cash_box_num as cashboxcode from view_sw_st_tvm_supplement_currency a "
                            + " join sw_ck_data_submit_log s on s.line_code='" + lineCode + "' and s.station_code='" + stationCode + "'  "
                            + " where a.line_code='" + lineCode + "' and a.station_code='" + stationCode + "' and a.tvm_cash_box_type='" + tvmCashBoxType + "' "
                            + " and a.supplement_currency_time between s.BEGIN_TIME and s.END_TIME "
                            + " and '" + revenueTime + "' BETWEEN DATE_FORMAT(s.BEGIN_TIME,'%Y-%m-%d') and DATE_FORMAT(date_add(s.END_TIME,interval -1 day),'%Y-%m-%d')";
                }
		    }
			if(tvmCode !=null && !tvmCode.equals("") && !"null".equals(tvmCode)){
		           sql = sql +" and a.tvm_code='"+tvmCode+"'";
		    }
		    sql = sql +" order by a.tvm_code"; 
			//log.info("-------------查询补录的SQL====="+sql );
			lstSupplement = JdbcUtil.exeSqlQueryByCon(sql);
		}catch(Exception e){
		
		}
		return lstSupplement;
	}
    
}
